use anyhow::{anyhow, bail};
use chrono::{Datelike, Days, Months, NaiveDate};
use shq_common::prelude::report::Unit;

use crate::Column;

use super::{analysis::{parse_params, DataLevel, TimeLevel}, RedisCache};

pub(super) async fn create_bar_line_chart(
    rept_name: &str,
    params: &[(&str, &str)],
    leaf_cols: &[&Column],
    rept_unit: &Unit,
    tmp_table_name: &str,
) -> anyhow::Result<()> {
    let data_level = params
        .iter()
        .find(|(k, _)| *k == "data_level")
        .map(|(_, v)| *v)
        .ok_or(anyhow::anyhow!("报表{rept_name}的data_level参数不存在"))?;
    let data_level=DataLevel::try_from_str(data_level)?;
    let table_name = get_dfkb_name(data_level.clone());
    let cuid_where=if data_level==DataLevel::CompClie || data_level==DataLevel::IndiClie {
        let cuid=params.iter().find(|(k,_)|*k=="cuid").map(|(_,v)|*v).ok_or(anyhow!("cuid参数不存在"))?;
        let cuid:u64=cuid.parse().or_else(|_err|bail!("参数cuid不是有效的整数"))?;
        format!("AND CUID={cuid}")
    }else{
        "".to_owned()
    };
    let (_, date, time_level, col_name, _, level_cause, cyno_cause) = parse_params(params)?;
    let time_level=time_level.ok_or(anyhow!("time_level参数不能为空"))?;
    let date_ranges = get_date_range(time_level, date, 12)?;
    let dlen=date_ranges.len();
    let mut sql = String::new();
    for (i,dr) in date_ranges.into_iter().enumerate() {
        let one_sql=format!(r#"with toDate('{}') AS SDATE,toDate('{}') AS EDATE,dateDiff(day,SDATE,EDATE)+1 AS DAYS
        select '{}' AS DTNM,sumIf({col_name},DTID=EDATE) as ACBL,sum({col_name})/DAYS as AVVL
        from {table_name}
        where DTID>=SDATE and DTID<=EDATE {cuid_where} {level_cause} {cyno_cause}"#,dr.start_date,dr.end_date,dr.name);
        sql.push_str(&one_sql);
        if i<dlen-1{
            sql.push_str(" UNION ALL ");
        }
    }
    RedisCache::new(tmp_table_name)?.cache(&sql,leaf_cols,rept_unit).await?;
    Ok(())
}
struct DateRange {
    name: String,
    start_date: NaiveDate,
    end_date: NaiveDate,
}
fn get_date_range(time_level: TimeLevel, date: &str, count: u8) -> anyhow::Result<Vec<DateRange>> {
    let date = NaiveDate::parse_from_str(date, "%Y%m%d")?;
    let count=count-1;
    let mut res: Vec<DateRange> = Vec::new();
    match time_level {
        TimeLevel::Year => {
            let eyear = date.year();
            let syear = eyear - count as i32;
            for year in syear..=eyear {
                let start_date =
                    NaiveDate::from_ymd_opt(year, 1, 1).ok_or(anyhow::anyhow!("日期无效"))?;
                let mut end_date =
                    NaiveDate::from_ymd_opt(year, 12, 31).ok_or(anyhow::anyhow!("日期无效"))?;
                if end_date > date {
                    end_date = date;
                }
                res.push(DateRange {
                    name: year.to_string(),
                    start_date,
                    end_date,
                });
            }
        }
        TimeLevel::Quarter => {
            let sdate = date
                .checked_sub_months(Months::new(3 * count as u32))
                .ok_or(anyhow::anyhow!("日期无效"))?;
            let month = sdate.month();
            let smonth = if month <= 3 {
                1
            } else if month <= 6 {
                4
            } else if month <= 9 {
                7
            } else {
                10
            };
            let mut start_date = NaiveDate::from_ymd_opt(sdate.year(), smonth, 1)
                .ok_or(anyhow::anyhow!("日期无效"))?;
            while start_date <= date {
                let year=start_date.year();
                let month = start_date.month();
                let (mut end_date,q) = if month <= 3 {
                    let edate=NaiveDate::from_ymd_opt(start_date.year(), 3, 31).ok_or(anyhow::anyhow!("日期无效"))?;
                    (edate,1u8)
                } else if month <= 6 {
                    let edate=NaiveDate::from_ymd_opt(start_date.year(), 6, 30).ok_or(anyhow::anyhow!("日期无效"))?;
                    (edate,2)
                } else if month <= 9 {
                    let edate=NaiveDate::from_ymd_opt(start_date.year(), 9, 30).ok_or(anyhow::anyhow!("日期无效"))?;
                    (edate,3)
                } else {
                    let edate=NaiveDate::from_ymd_opt(start_date.year(), 12, 31).ok_or(anyhow::anyhow!("日期无效"))?;
                    (edate,4)
                };
                if end_date > date {
                    end_date = date;
                }
                res.push(DateRange {
                    name: format!("{}Q{}", year, q),
                    start_date,
                    end_date,
                });
                start_date = start_date
                    .checked_add_months(Months::new(3))
                    .ok_or(anyhow::anyhow!("日期无效"))?;
            }
        }
        TimeLevel::Month => {
            let sdate = date
                .checked_sub_months(Months::new(count as u32))
                .ok_or(anyhow::anyhow!("日期无效"))?;
            let mut start_date = NaiveDate::from_ymd_opt(sdate.year(), sdate.month(), 1)
                .ok_or(anyhow::anyhow!("日期无效"))?;
            while start_date <= date {
                let mut end_date = start_date
                    .checked_add_months(Months::new(1))
                    .ok_or(anyhow::anyhow!("日期无效"))?
                    .checked_sub_days(Days::new(1))
                    .ok_or(anyhow::anyhow!("日期无效"))?;
                if end_date > date {
                    end_date = date;
                }
                res.push(DateRange {
                    name: start_date.format("%Y%m").to_string(),
                    start_date,
                    end_date,
                });
                start_date = start_date
                    .checked_add_months(Months::new(1))
                    .ok_or(anyhow::anyhow!("日期无效"))?;
            }
        }
        TimeLevel::Day => {
            let mut start_date = date
                .checked_sub_days(Days::new(count as u64))
                .ok_or(anyhow::anyhow!("日期无效"))?;
            let end_date = date;
            while start_date <= date {
                res.push(DateRange {
                    name: start_date.format("%Y%m%d").to_string(),
                    start_date,
                    end_date,
                });
                start_date = start_date
                    .checked_add_days(Days::new(1))
                    .ok_or(anyhow::anyhow!("日期无效"))?;
            }
        }
    }
    Ok(res)
}

pub (super) fn get_dfkb_name(data_level: DataLevel)->&'static str{
    match data_level {
        DataLevel::CompClie=>"DFKB_COMP_CLIE_INDEX",
        DataLevel::IndiClie=>"DFKB_INDI_CLIE_INDEX",
        _=>"DFKB_EMPL_INDEX",
    }
}
